﻿CREATE PROCEDURE [dbo].[utl_AssignRoles]

AS
BEGIN
	DECLARE @ID                INT
	DECLARE @SQL               NVARCHAR(MAX)
	DECLARE @DeveloperAccount  sysname
	
	SELECT @ID = MIN(d.ID)
	FROM   #Developer d
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @DeveloperAccount = d.DeveloperAccount
	    FROM   #Developer d
	    WHERE  d.ID = @ID
	    
	    SET @SQL = 'EXECUTE sp_addrolemember @rolename = ''db_datareader'', @membername = [' + @DeveloperAccount + ']'
	    EXECUTE sp_executesql @SQL

	    SET @SQL = 'EXECUTE sp_addrolemember @rolename = ''db_datawriter'', @membername = [' + @DeveloperAccount + ']'
	    EXECUTE sp_executesql @SQL
	    
   	    SET @SQL = 'GRANT VIEW DEFINITION TO [' + @DeveloperAccount + ']'
	    EXECUTE sp_executesql @SQL

	    SELECT @ID = MIN(d.ID)
	    FROM   #Developer d
	    WHERE  d.ID > @ID
	END
END

